package com.offcn.dao.impl;

import com.offcn.dao.UserDao;
import com.offcn.entity.Paging;
import com.offcn.entity.User;
import com.offcn.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {

    private QueryRunner qr = new QueryRunner();

    //登录的方法
    public User login(String username, String password) throws SQLException {
        Connection conn = DruidUtils.getConnection();
        String sql = "select * from user where username = ? and password = ?";
        Object[] param = {username,password};
        User user = qr.query(conn, sql, new BeanHandler<User>(User.class), param);
        conn.close();
        return user;
    }

    //查询总条数
    public int queryTotalCount(String usernameSearch) throws SQLException {
        Connection conn = DruidUtils.getConnection();
        String sql = "select count(*) from user ";
        if (usernameSearch != null){
            sql += " where username like '%"+ usernameSearch +"%'";
        }
        Long totalCount = (Long)qr.query(conn, sql, new ScalarHandler());
        conn.close();
        return totalCount.intValue();
    }
    //查询并分页展示的方法
    public List<User> findUserByPage(Paging page, String usernameSearch) throws SQLException {
        Connection conn = DruidUtils.getConnection();
        StringBuilder builder = new StringBuilder();
        builder.append("select * from user ");
        if (usernameSearch != null){
            builder.append(" where username like '%"+ usernameSearch +"%' ");
        }
        builder.append(" limit "+page.getStartIndex()+","+page.getPageSize());
        List<User> list = qr.query(conn, builder.toString(), new BeanListHandler<User>(User.class));
        conn.close();
        return list;
    }
    //修改用户的方法
    public int modifyUser(User user) throws SQLException {
        Connection conn = DruidUtils.getConnection();
        String sql = "update user set name = ?,phone = ?,age = ?,sex=?,username=?,password=?," +
                "status=?,createtime=?,role=? where uid = ?";
        Object[] params = {user.getName(),user.getPhone(),user.getAge(),user.getSex(),
        user.getUsername(),user.getPassword(),user.getStatus(),user.getCreatetime(),
        user.getRole(),user.getUid()};
        int row = qr.update(conn, sql, params);
        conn.close();
        return row;
    }

    //根据选择框所选进行删除，where id = ?
    /*public int DeleteAccordingSelection(String[] split) throws SQLException {
        Connection conn = DruidUtils.getConnection();
        String sql = "DELETE FROM USER WHERE uid IN (?)";
        Object[] param = split;
        int row = qr.update(conn, sql, param);
        conn.close();
        return row;
    }*/
    public int DeleteAccordingSelection(String delarr) throws SQLException {
        Connection conn = DruidUtils.getConnection();
        String sql = "DELETE FROM USER WHERE uid IN ("+delarr+")";
        //Object[] param = {delarr};
        int row = qr.update(conn, sql);
        conn.close();
        return row;
    }
    //新增用户的方法
    public int addUser(User user) throws SQLException {
        Connection conn = DruidUtils.getConnection();
        String sql = "INSERT INTO USER (name,phone,age,sex,username,password,status,role) " +
                "VALUES (?,?,?,?,?,?,?,?)";
        Object[] param = {user.getName(),user.getPhone(),user.getAge(),user.getSex(),
                user.getUsername(),user.getPassword(),user.getStatus(),user.getRole()};
        int row = qr.update(conn, sql, param);
        return row;
    }


}
